package com.data.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

import java.util.Locale;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;


public class FromExcel {
	FileInputStream fs = null;
	HSSFWorkbook wb   = null;
	public void openExcel(String filePath){
		
		try {
			fs = new FileInputStream(new File(filePath));
			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		

        }
	public FileInputStream getExcelFile(){
		return fs;
	}
	
	public void setWorkbook(){
		try {
			wb = new HSSFWorkbook(getExcelFile());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		/*ws = new WorkbookSettings();
		ws.setLocale(new Locale("en", "EN"));
		try {
			workbook = Workbook.getWorkbook(getExcelFile(), ws);
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
	}
	public HSSFWorkbook getWorkbook(){
		
		return wb;
	}
	public String[] getCredentials(){
		HSSFSheet s = wb.getSheetAt(0);  
		//Sheet s = workbook.getSheet(0);
		HSSFRow row     = s.getRow(1); 
		
		String[] userCredentials = new String[2];
		userCredentials[0] = row.getCell(0, Row.CREATE_NULL_AS_BLANK).toString();
		userCredentials[1] =  row.getCell(1, Row.CREATE_NULL_AS_BLANK).toString();
		return userCredentials;
	}
	
	public int getNumberOfTestCases(){
		HSSFSheet s = wb.getSheetAt(1);  
		int rowCount;
		//Total Total No Of Rows in Sheet, will return you no of rows that are occupied with some data
		//System.out.println("Total Rows inside Sheet:" + s.getRows());
		if(s.getPhysicalNumberOfRows()!=0){
		 rowCount = s.getLastRowNum();
		}else{
			rowCount=0;
		}
		return rowCount;
	}
	
	public HSSFSheet getTestResults(){
		HSSFSheet s = wb.getSheetAt(1);  
		
		return s;
		
	}
	//Returns the Headings used inside the excel sheet
		public void getHeadingFromXlsFile(HSSFSheet sheet) {
			int columnCount = sheet.getRow(0).getLastCellNum()-1;
			for (int i = 0; i < columnCount; i++) {
				System.out.println(sheet.getRow(0).getCell(i));
			}
		}  
        public static String getCurrentDateAndTime(){
        	Date date = new Date(); 
        	SimpleDateFormat formatteryear = new SimpleDateFormat("yyyy-MM-dd");//2012-06-12T18:00:00.000Z
        	SimpleDateFormat formattime = new SimpleDateFormat("HH:mm:ss");
			return formatteryear.format(date)+"T"+formattime.format(date)+".0000Z";
        	
        }
		public void saveWorkBook(String filePath) throws IOException {
			// TODO Auto-generated method stub
			 FileOutputStream out = new FileOutputStream(filePath);
		     wb.write(out);
		}

	}

